package com.sxfq.loansupermarket.backsystem.modular.refunds.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.TcInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.PerformanceInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.SearchPerformanceInfo;
import com.sxfq.loansupermarket.backsystem.modular.refunds.service.IPerformanceService;
import com.sxfq.loansupermarket.backsystem.modular.system.transfer.UserDto;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.DepartmentInfo;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/performance")
public class PerformanceController extends BaseController {
    @Autowired
    private IPerformanceService service;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/balance/";

    /**
     * 跳转费用明细页面
     */
    @RequestMapping(value = "")
    public String recharge(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Abbreviation",0)+"";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='"+tableHead+"' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList =sqlMapper.selectList(getListSql,TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"pay_service_grade_info",0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList=sqlMapper.selectList(getListSql,DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getSessionAttr("archivingList") == null) {
            String sql = "select * from user where roleid in " +
                    "(select id from role a where a.pid=(select id from role a where a.name='市场')" +
                    "union all select id from role a where  a.name='市场')  group by id;";
            List<UserDto> archivingList = sqlMapper.selectList(sql, UserDto.class);
            ShiroKit.setSessionAttr("archivingList", archivingList);
        }
        model.addAttribute("departmentList",ShiroKit.getUser().getDepartmentList());
        model.addAttribute("archivingList", ShiroKit.getSessionAttr("archivingList"));
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("outpatientNameValue", ShiroKit.getUser().getOutpatientName());
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("auditswitch",redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"Auditswitch",0));
        model.addAttribute("sttleAccountsTime", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"SttleAccountsTime",0));
        return PREFIX + "performance.html";
    }

    /**
     * 业绩查询列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object selectAchievementList(SearchPerformanceInfo info, HttpServletRequest request) {
        Page<PerformanceInfo> page = new PageFactory<PerformanceInfo>().defaultPage();
        try {
            info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
            info.setLimitIndex(page.getSize());
            page.setCurrent(0);
            List<PerformanceInfo> list = service.selectPageListQueryPages(page, info);
            if (list.size() == 0) {
                info.setStartIndex(0);
                list = service.selectPageListQueryPages(page, info);
            }
            page.setRecords(list);
            page.setTotal(service.getQueryPagesCount(page, info));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return super.packForBT(page);
    }

    /**
     * 统计查询.
     *
     */
    @RequestMapping(value = "/total", method = RequestMethod.GET)
    @ResponseBody
    public PerformanceInfo total(String reach, String startRechargeTime, String endRechargeTime, String oneChannel1, String twoChannel1,
                                 String thrChannel1,String oneMedium1, String twoMedium1,
                                 String thrMedium1, String scarchiving1, String tcName1, String outpatientName,
                                 String isDoName1, String treatmentUnit1, String customerStatus1) throws Exception {
        String getTableHeadsql = "select abbreviation from dept t where t.simplename='" + outpatientName + "' limit 1";
        Dept dept = sqlMapper.selectOne(getTableHeadsql, Dept.class);
        String tableHead = dept.getAbbreviation();
        String tableNamec = tableHead + "customer_info";
        String tableNamed = tableHead + "deal_info";
        String tableNamer = tableHead + "recharge_info";
        String contitionInfo = " ";
        String contitionInfos = " ";
        if (StringUtil.isNotEmpty(startRechargeTime)) {
            contitionInfo += " and t1.recharge_time >='" + startRechargeTime + "'";
        }
        if (StringUtil.isNotEmpty(endRechargeTime)) {
            contitionInfo += " and t1.recharge_time <='" + endRechargeTime + "'";
        }
        if (StringUtil.isNotEmpty(outpatientName)) {
            contitionInfo += " and t.arrival_store ='" + outpatientName + "'";
        }
        if (StringUtil.isNotEmpty(customerStatus1)) {
            String customerStatus = "";
            String customerStatus2[] = customerStatus1.split(",");
            if (customerStatus2.length == 1) {
                customerStatus = customerStatus2[0];
            } else {
                for (int i = 0; i < customerStatus2.length; i++) {
                    if (i == (customerStatus2.length - 1)) {
                        customerStatus += "'" + customerStatus2[i];
                    } else if (i == 0) {
                        customerStatus += customerStatus2[i] + "'" + ",";
                    } else {
                        customerStatus += "'" + customerStatus2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.customer_status in ('" + customerStatus + "')";
        }
        String isEarnes=" ";
        String isEarnes1=" ";
        if (StringUtil.isNotEmpty(isDoName1)) {
            if ("9".equals(isDoName1)) {
                isEarnes +=" and t1.isEarnes=2";
                isEarnes1 +=" and t1.isEarnes=10";
            }else  if ("10".equals(isDoName1)) {
                isEarnes +=" and t1.isEarnes=10";
                isEarnes1 +=" and t1.isEarnes=10";
            } else{
                contitionInfo += " and t2.is_do = '" + isDoName1 + "'";
                contitionInfos += " and t2.is_do = '9'";
            }
        }else{
            isEarnes +=" and t1.isEarnes in (2,10)";
        }
        if (StringUtil.isNotEmpty(oneChannel1)) {
            if (oneChannel1.indexOf("请选择") == -1) {
                String oneChannel = "";
                String oneChannel2[] = oneChannel1.split(",");
                if (oneChannel2.length == 1) {
                    oneChannel = oneChannel2[0];
                } else {
                    for (int i = 0; i < oneChannel2.length; i++) {
                        if (i == (oneChannel2.length - 1)) {
                            oneChannel += "'" + oneChannel2[i];
                        } else if (i == 0) {
                            oneChannel += oneChannel2[i] + "'" + ",";
                        } else {
                            oneChannel += "'" + oneChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.one_channel in ('" + oneChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(twoChannel1)) {
            if (twoChannel1.indexOf("请选择") == -1) {
                String twoChannel = "";
                String twoChannel2[] = twoChannel1.split(",");
                if (twoChannel2.length == 1) {
                    twoChannel = twoChannel2[0];
                } else {
                    for (int i = 0; i < twoChannel2.length; i++) {
                        if (i == (twoChannel2.length - 1)) {
                            twoChannel += "'" + twoChannel2[i];
                        } else if (i == 0) {
                            twoChannel += twoChannel2[i] + "'" + ",";
                        } else {
                            twoChannel += "'" + twoChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.two_channel in ('" + twoChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(thrChannel1)) {
            if (thrChannel1.indexOf("请选择") == -1) {
                String thrChannel = "";
                String thrChannel2[] = thrChannel1.split(",");
                if (thrChannel2.length == 1) {
                    thrChannel = thrChannel2[0];
                } else {
                    for (int i = 0; i < thrChannel2.length; i++) {
                        if (i == (thrChannel2.length - 1)) {
                            thrChannel += "'" + thrChannel2[i];
                        } else if (i == 0) {
                            thrChannel += thrChannel2[i] + "'" + ",";
                        } else {
                            thrChannel += "'" + thrChannel2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.thr_channel in ('" + thrChannel + "')";
            }
        }
        if (StringUtil.isNotEmpty(oneMedium1)) {
            if (oneMedium1.indexOf("请选择") == -1) {
                String oneMedium = "";
                String oneMedium2[] = oneMedium1.split(",");
                if (oneMedium2.length == 1) {
                    oneMedium = oneMedium2[0];
                } else {
                    for (int i = 0; i < oneMedium2.length; i++) {
                        if (i == (oneMedium2.length - 1)) {
                            oneMedium += "'" + oneMedium2[i];
                        } else if (i == 0) {
                            oneMedium += oneMedium2[i] + "'" + ",";
                        } else {
                            oneMedium += "'" + oneMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.one_Medium in ('" + oneMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(twoMedium1)) {
            if (twoMedium1.indexOf("请选择") == -1) {
                String twoMedium = "";
                String twoMedium2[] = twoMedium1.split(",");
                if (twoMedium2.length == 1) {
                    twoMedium = twoMedium2[0];
                } else {
                    for (int i = 0; i < twoMedium2.length; i++) {
                        if (i == (twoMedium2.length - 1)) {
                            twoMedium += "'" + twoMedium2[i];
                        } else if (i == 0) {
                            twoMedium += twoMedium2[i] + "'" + ",";
                        } else {
                            twoMedium += "'" + twoMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.two_Medium in ('" + twoMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(thrMedium1)) {
            if (thrMedium1.indexOf("请选择") == -1) {
                String thrMedium = "";
                String thrMedium2[] = thrMedium1.split(",");
                if (thrMedium2.length == 1) {
                    thrMedium = thrMedium2[0];
                } else {
                    for (int i = 0; i < thrMedium2.length; i++) {
                        if (i == (thrMedium2.length - 1)) {
                            thrMedium += "'" + thrMedium2[i];
                        } else if (i == 0) {
                            thrMedium += thrMedium2[i] + "'" + ",";
                        } else {
                            thrMedium += "'" + thrMedium2[i] + "'" + ",";
                        }
                    }
                }
                contitionInfo += " and t.thr_Medium in ('" + thrMedium + "')";
            }
        }
        if (StringUtil.isNotEmpty(treatmentUnit1)) {
            String treatmentUnit = "";
            String treatmentUnit2[] = treatmentUnit1.split(",");
            if (treatmentUnit2.length == 1) {
                treatmentUnit = treatmentUnit2[0];
            } else {
                for (int i = 0; i < treatmentUnit2.length; i++) {
                    if (i == (treatmentUnit2.length - 1)) {
                        treatmentUnit += "'" + treatmentUnit2[i];
                    } else if (i == 0) {
                        treatmentUnit += treatmentUnit2[i] + "'" + ",";
                    } else {
                        treatmentUnit += "'" + treatmentUnit2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.treatment_unit in ('" + treatmentUnit + "')";
        }
        if (StringUtil.isNotEmpty(scarchiving1)) {
            String scarchiving = "";
            String scarchiving2[] = scarchiving1.split(",");
            if (scarchiving2.length == 1) {
                scarchiving = scarchiving2[0];
            } else {
                for (int i = 0; i < scarchiving2.length; i++) {
                    if (i == (scarchiving2.length - 1)) {
                        scarchiving += "'" + scarchiving2[i];
                    } else if (i == 0) {
                        scarchiving += scarchiving2[i] + "'" + ",";
                    } else {
                        scarchiving += "'" + scarchiving2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and t.scarchiving in ('" + scarchiving + "')";
        }
        if (StringUtil.isNotEmpty(tcName1)) {
            String tcName = "";
            String tcName2[] = tcName1.split(",");
            if (tcName2.length == 1) {
                tcName = tcName2[0];
            } else {
                for (int i = 0; i < tcName2.length; i++) {
                    if (i == (tcName2.length - 1)) {
                        tcName += "'" + tcName2[i];
                    } else if (i == 0) {
                        tcName += tcName2[i] + "'" + ",";
                    } else {
                        tcName += "'" + tcName2[i] + "'" + ",";
                    }
                }
            }
            contitionInfo += " and (t2.tc_name in ('" + tcName + "') or t1.tc_name in ('" + tcName + "'))";
        }
        if (StringUtils.isNotBlank(reach)) {
            contitionInfo += " and (t.customer_name like CONCAT('%','" + reach + "','%') or t.customer_phone like CONCAT('%','" + reach + "','%') " +
                    " or t.wechat_number like CONCAT('%','" + reach + "','%') or t.medical_record_number like CONCAT('%','" + reach + "','%') " +
                    " or t.szm like CONCAT('%','" + reach + "','%') or t.customer_phone2 like CONCAT('%','" + reach + "','%'))";
        }
        String sql = " " +
                "select ifnull(sum(serviceActualPay),0) as hospitalPerformance,ifnull(sum(serviceActualPay)+sum(depositAmount),0) as sectionPerformance," +
                "   count(distinct(t.reservationsId)) as number\n" +
                "  from (select t.reservationsId as reservationsId,t.getTime as getTime,\n" +
                "   sum(t.serviceActualPay) as serviceActualPay,\n" +
                "   sum(t.depositAmount) as depositAmount\n" +
                "   from (select t1.reservations_id as reservationsId,t2.get_time as getTime,\n" +
                "   t2.service_actual_pay as serviceActualPay,\n" +
                "   t2.deposit_amount as depositAmount\n" +
                "   from "+tableNamed+" t2\n" +
                "   left join "+tableNamer+" t1 on t1.deal_id=t2.id\n" +
                "   left join "+tableNamec+" t on t.id=t1.customer_id\n" +
                "   where 1=1\n" +isEarnes1+
                "   "+contitionInfo+"" +
                "   group by t2.id) t group by t.getTime,t.reservationsId\n" +
                "   ) t ; ";
        PerformanceInfo tongJiInfoList1 = sqlMapper.selectOne(sql, PerformanceInfo.class);
        String sql2 = " " +
                "select ifnull(sum(serviceActualPay),0) as hospitalPerformance ,0 as sectionPerformance,count(distinct(t.reservationsId)) as number\n" +
                "  from (select t.reservationsId as reservationsId,t.getTime as getTime,\n" +
                "   sum(t.serviceActualPay) as serviceActualPay,\n" +
                "   sum(t.depositAmount) as depositAmount\n" +
                "   from ("+
                "   select t.reservations_id as reservationsId,'' as getTime,\n" +
                "   t1.actual_account_balance as serviceActualPay,\n" +
                "   0 as depositAmount\n" +
                "   from "+tableNamer+" t1\n" +
                "   left join "+tableNamec+" t on t.id=t1.customer_id\n" +
                "   left join "+tableNamed+" t2 on t.id=t2.customer_id\n" +
                "   where 1=1  " +isEarnes+
                "   "+contitionInfo+"" +
                "   "+contitionInfos+"" +
                "   group by t1.id) t) t ; ";
        PerformanceInfo tongJiInfoList2 = sqlMapper.selectOne(sql2, PerformanceInfo.class);
        PerformanceInfo tongJiInfo = new PerformanceInfo();
        tongJiInfo.setHospitalPerformance(tongJiInfoList1.getHospitalPerformance()+tongJiInfoList2.getHospitalPerformance());
        tongJiInfo.setSectionPerformance(tongJiInfoList1.getSectionPerformance()+tongJiInfoList2.getSectionPerformance());
        tongJiInfo.setNumber(tongJiInfoList1.getNumber()+tongJiInfoList2.getNumber());
        return tongJiInfo;
    }

    @RequestMapping(value = "/performanceTableExport")
    public void performanceTableExport(@Valid SearchPerformanceInfo searchInfo, HttpServletResponse response) {
        try {
            List<PerformanceInfo> list = service.getperformanceTableExport(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 32; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("电话");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("结账时间");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("新老客");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("交易类型");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("科室");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("应收");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("实收");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("实到金额");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("储值划扣");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("优惠抵扣");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("全院业绩");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("科室业绩");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("一级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(18);
            cell.setCellValue("二级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(19);
            cell.setCellValue("三级渠道");
            cell.setCellStyle(style);
            cell = row.createCell(20);
            cell.setCellValue("一级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(21);
            cell.setCellValue("二级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(22);
            cell.setCellValue("三级媒介");
            cell.setCellStyle(style);
            cell = row.createCell(23);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(24);
            cell.setCellValue("推荐人");
            cell.setCellStyle(style);
            cell = row.createCell(25);
            cell.setCellValue("护士");
            cell.setCellStyle(style);
            cell = row.createCell(26);
            cell.setCellValue("门店");
            cell.setCellStyle(style);
            cell = row.createCell(27);
            cell.setCellValue("网电咨询师");
            cell.setCellStyle(style);
            cell = row.createCell(28);
            cell.setCellValue("正畸编号");
            cell.setCellStyle(style);
            cell = row.createCell(29);
            cell.setCellValue("转诊人");
            cell.setCellStyle(style);
            cell = row.createCell(30);
            cell.setCellValue("被转诊人");
            cell.setCellStyle(style);
            cell = row.createCell(31);
            cell.setCellValue("转诊时间");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                PerformanceInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 32; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getMedicalRecordNumber());
                row.getCell(1).setCellValue(map.getCustomerName());
                if(Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount()+"PhonePrivilegesexp",0)+"")==1) {
                    row.getCell(2).setCellValue(map.getCustomerPhone());
                }else{
                    row.getCell(2).setCellValue("无");
                }
                row.getCell(3).setCellValue(map.getSex());
                row.getCell(4).setCellValue(map.getCustomerAge());
                row.getCell(5).setCellValue(map.getArrivalTime());
                row.getCell(6).setCellValue(map.getRechargeTime());
                row.getCell(7).setCellValue(map.getCustomerStatus());
                if (map.getIsDo() == 3) {
                    row.getCell(8).setCellValue("还欠款");
                } else if (map.getIsDo() == 4) {
                    row.getCell(8).setCellValue("退款");
                } else if (map.getIsDo() == 5) {
                    row.getCell(8).setCellValue("消费");
                } else if (map.getIsDo() == 9) {
                    row.getCell(8).setCellValue("储值变动");
                } else {
                    row.getCell(8).setCellValue("其他");
                }
                row.getCell(9).setCellValue(map.getTreatmentUnit());
                if (map.getIsDo() == 5) {  //财务应收=应收-本次优惠抵扣
                    row.getCell(10).setCellValue(map.getServiceActualPay()+map.getAccountArrears2()
                            +map.getDeductionAmount()+map.getDepositAmount());
                } else if (map.getIsDo() == 3) {   //财务应收=0
                    row.getCell(10).setCellValue(0);
                } else if (map.getIsDo() == 4) {  //财务应收=实收+欠费变动
                    row.getCell(10).setCellValue(map.getServiceActualPay()+map.getAccountArrears2()
                            +map.getDeductionAmount()+map.getDepositAmount());
                } else if (map.getIsDo() == 9) {  //财务应收=实收
                    row.getCell(10).setCellValue(0);
                }
                row.getCell(11).setCellValue(map.getServiceActualPay());  //实收
                row.getCell(12).setCellValue(map.getServiceActualPay()*(map.getEquation()/100)); //实到金额
                row.getCell(13).setCellValue(map.getDepositAmount());
                row.getCell(14).setCellValue(map.getDeductionAmount());
                row.getCell(15).setCellValue(map.getServiceActualPay());
                row.getCell(16).setCellValue(map.getServiceActualPay()+map.getDepositAmount());
                row.getCell(17).setCellValue(map.getOneChannel());
                row.getCell(18).setCellValue(map.getTwoChannel());
                row.getCell(19).setCellValue(map.getThrChannel());
                row.getCell(20).setCellValue(map.getOneMedium());
                row.getCell(21).setCellValue(map.getTwoMedium());
                row.getCell(22).setCellValue(map.getThrMedium());
                row.getCell(23).setCellValue(map.getTcName());
                row.getCell(24).setCellValue(map.getReferee());
                row.getCell(25).setCellValue(map.getNurse());
                row.getCell(26).setCellValue(map.getOutpatientName());
                row.getCell(27).setCellValue(map.getScarchiving());
                row.getCell(28).setCellValue(map.getOrthodonticNumber());
                row.getCell(29).setCellValue(map.getReferralerPerson());
                row.getCell(30).setCellValue(map.getReferralerPersoned());
                row.getCell(31).setCellValue(map.getReferralerTime());

            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}